Create Dictionary Tables in ODS Database and Populate the Dictionary Tables 11

Download the Sql File attached to the 

Run Script that is attached to create your Dictionary tables in the ODS Database. Once ran the tables will be made up of these columns.




After creating the Dictionary tables in ODS Database- use SSIS to populate the Dictionary tables using the OLDB_Source as source.  To load the clean data into the Dictionary tables - used Derived Column Transformation and Data Conversion Transformation to load the Staging Files into OLDB_Destination ie Dictionary tables



Repeat the same steps to load all the dictionary tables. 



DTF - Dictionary Incident Details
Derived Column


Data Conversion


Lookup


DFT - - Dictionary Migration -the code used as source table is provided below .

select   State,   code, 

  case when [dbo].[USStatesAndCode_Stg].State in (
      'Alabama', 'Connecticut', 'Delaware',     'Florida', 'Georgia', 'Kentucky',     'Louisiana', 'Maine', 'Maryland', 
    'Massachusetts', 'Michigan', 'Mississippi',     'New Hampshire', 'New Jersey', 'New York',     'North Carolina', 'Ohio', 'Pennsylvania', 
    'Rhode Island', 'South Carolina',     'Tennessee', 'Vermont', 'Virginia',     'West Virginia'  ) then 'YES' end AtlanticFlyway, 
  case when [dbo].[USStatesAndCode_Stg].State in (
      'Alaska', 'Colorado', 'Kansas', 'Montana',     'Nebraska', 'New Mexico', 'North Dakota',     'Oklahoma', 'South Dakota', 'Texas', 
    'Utah', 'Wyoming'  ) then 'YES' end CentralFlyway, 
  case when [dbo].[USStatesAndCode_Stg].State in (
    'Alaska', 'Arizona', 'California',     'Colorado', 'Idaho', 'New Mexico',     'Nevada', 'Montana', 'Oregon', 'Utah', 
                     'Washington', 'Wyoming'  ) then 'YES' end PacificFlyway, 
  case when [dbo].[USStatesAndCode_Stg].State in (
      'Alabama', 'Alaska', 'Arkansas', 'Indiana',     'Illinois', 'Iowa', 'Kansas', 'Kentucky',     'Louisiana', 'Ohio', 'Oklahoma', 
                          'Michigan', 'Missouri', 'Mississippi',     'Nebraska', 'North Dakota', 'South Dakota',     'Tennessee', 'Texas', 'Wisconsin'
  ) then 'YES' else null end MississippiFlyway 
from   [dbo].[USStatesAndCode_Stg]


Derived Column


Data Conversion

Lookup



DTF - Dictionary Airport Details
Derived Column


Data Conversion



Lookup



DTF- Dictionary Airlines Details
Derived Column


Data Conversion


Lookup



DTF - Dictionary Make&Model Details
Derived Column


Data Conversion


Lookup


DFT- Dictionary PilotsWarned Details
Derived Column


Data Conversion  


Lookup


DTF -Dictionary Aircraft Details
Derived Column


Num_Engs

ISNULL(NUMENGS) || NUMENGS == "" ? NULL(DT_I4) :((DT_I4)NUMENGS)

Case statement used to populate the Engine_Power Column - 

TYPEENG == "A" ? "Reciprocating Engine" : TYPEENG == "B" ? "Turbojet" : TYPEENG == "C" ? "Turboprop" : TYPEENG == "D" ? "Turbofan" : TYPEENG == "E" ? "Glider" : TYPEENG == "F" ? "Turboshaft" : "Other"

Case statement used to populate the Airplane_MASS Column - 

ACMASS == "1" ? "2,250 kg or less" : ACMASS == "2" ? "2251-5700 kg" : ACMASS == "3" ? "5,701-27,000 kg" : ACMASS == "4" ? "27,001-272,000 kg" : "above 272,000 kg"

Case statement used to populate the Airplane_Types Column - 

ACCLASS == "A" ? "Airplane" : ACCLASS == "B" ? "Helicopter" : ACCLASS == "C" ? "Glider" : ACCLASS == "D" ? "Balloon" : ACCLASS == "F" ? "Dirigible" : ACCLASS == "I" ? "Gyroplane" : ACCLASS == "J" ? "Ultralight" : "Other"

 

Data Conversion


Lookup


 

DTF -  Dictionary Flight Details
Derived Column


Code for SpeedOfFlight & HeightOfFlight

ISNULL(SPEED) || SPEED == "" ? NULL(DT_I4) : ((DT_I4)SPEED)
ISNULL(HEIGHT) || HEIGHT == "" ? NULL(DT_I4) : ((DT_I4)HEIGHT)

Data Conversion


Lookup



DFT - Dictionary Brid Details
Derived Column


Data Conversion


Lookup


DFT- Dictionary Damages Details 

Derived Column


Case statement used to populate the OutOfService Column â€“ 

ISNULL(AOS) || AOS == "" ? NULL(DT_I4) : ((DT_I4)AOS)

Case statement used to populate the DamageDescription Column - 

DAMAGELEVEL == "N" ? "None" : DAMAGELEVEL == "M" ? "Minor" : DAMAGELEVEL == "M?" ? "Uncertain Level" : DAMAGELEVEL == "S" ? "Substantial" : DAMAGELEVEL == "D" ? "Destroyed" : DAMAGELEVEL == "Class A" ? "Damage Over $2,000,000 (Military)" : DAMAGELEVEL == "Class B" ? "Damage Between $500,000 and $2,000,000 (Military)" : DAMAGELEVEL == "Class C" ? "Damage between $50,000 amd $499,999 (Military)" : DAMAGELEVEL == "Class D" ? "Damage between $20,000 and $49,999 (Military)" : DAMAGELEVEL == "Class E" ? "Damage less than $20,000 (Military)" : DAMAGELEVEL

Case statement used to populate the EFFECT Column - 

EFFECT == "Aborted Take-off, Other" ? REPLACE(EFFECT,"Aborted Take-off, Other","Aborted Take-off") : EFFECT == "None, Precautionary Landing" ? REPLACE(EFFECT,"None, Precautionary Landing","Precautionary Landing") : EFFECT == "Other, Precautionary Landing" ? REPLACE(EFFECT,"Other, Precautionary Landing","Precautionary Landing") : EFFECT == "None, Other" ? REPLACE(EFFECT,"None, Other","Other") : EFFECT

Data Conversion


Lookup

 

 

DFT- Seasons Details Dictionary

Derived Column


Case statement used to populate the MonthNameColumn - 

INCIDENTMONTH == 1 ? "January" : INCIDENTMONTH == 2 ? "February" : INCIDENTMONTH == 3 ? "March" : INCIDENTMONTH == 4 ? "April" : INCIDENTMONTH == 5 ? "May" : INCIDENTMONTH == 6 ? "June" : INCIDENTMONTH == 7 ? "July" : INCIDENTMONTH == 8 ? "August" : INCIDENTMONTH == 9 ? "September" : INCIDENTMONTH == 10 ? "October" : INCIDENTMONTH == 11 ? "November" : INCIDENTMONTH == 12 ? "December" : "Invalid Month"

Case statement used to populate the Seasons Column - MonthName

(INCIDENTMONTH == 12 || INCIDENTMONTH == 1 || INCIDENTMONTH == 2) ? "Winter" : (INCIDENTMONTH == 3 || INCIDENTMONTH == 4 || INCIDENTMONTH == 5) ? "Spring" : (INCIDENTMONTH == 6 || INCIDENTMONTH == 7 || INCIDENTMONTH == 8) ? "Summer" : (INCIDENTMONTH == 9 || INCIDENTMONTH == 10 || INCIDENTMONTH == 11) ? "Fall" : "Invalid Season"

Lookup



DFT - Tbl_BirdStudy

Derived Column


Code for DamagedIndicator, CostsRepair, OtherCosts, FatalitiesNR,InjuriesNR

INDICATED_DAMAGE == "TRUE" ? 1 : INDICATED_DAMAGE == "FALSE" ? 0 : 2
ISNULL(COSTREPAIRS) || COSTREPAIRS == "" ? NULL(DT_I4) : ((DT_I4)COSTREPAIRS)
ISNULL(OTHERCOST) || OTHERCOST == "" ? NULL(DT_I4) : ((DT_I4)OTHERCOST)
ISNULL(NRFATALITIES) || NRFATALITIES == "" ? NULL(DT_I4) : ((DT_I4)NRFATALITIES)
ISNULL(NRINJURIES) || NRINJURIES == "" ? NULL(DT_I4) : ((DT_I4)NRINJURIES)

 

 



Execute the package to populate the staging tables with the data.


The Dictionary tables is populated with Data.